package com.yangyang.notepad;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class NoteDatabaseHelper extends SQLiteOpenHelper {
    public static final String DATABASE_NAME = "notepad.db";
    private static final int DATABASE_VERSION = 1;

    private static final String TABLE_NOTES = "notes";
    private static final String COLUMN_ID = "_id";
    private static final String COLUMN_TITLE = "title";
    private static final String COLUMN_CONTENT = "content";
    private static final String COLUMN_CREATE_TIME = "create_time";
    private static final String COLUMN_UPDATE_TIME = "update_time";

    public NoteDatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE = "CREATE TABLE " + TABLE_NOTES + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY,"
                + COLUMN_TITLE + " TEXT,"
                + COLUMN_CONTENT + " TEXT,"
                + COLUMN_CREATE_TIME + " INTEGER DEFAULT (strftime('%s','now')),"
                + COLUMN_UPDATE_TIME + " INTEGER DEFAULT (strftime('%s','now'))"
                + ")";
        db.execSQL(CREATE_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_NOTES);
        onCreate(db);
    }

//    @Override
//    public SQLiteDatabase getWritableDatabase() {
//        File dbFile = new File(Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DCIM), DATABASE_NAME);
//        return SQLiteDatabase.openOrCreateDatabase(dbFile, null);
//    }

    // 添加新笔记
    public long addNote(Note note) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, note.getTitle());
        values.put(COLUMN_CONTENT, note.getContent());
        long id = db.insert(TABLE_NOTES, null, values);
        db.close();
        return id;
    }

    // 新增方法：专用于导入带时间戳的笔记
    public long importNoteWithTimestamps(Note note) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();

        // 基础字段
        values.put(COLUMN_TITLE, note.getTitle());
        values.put(COLUMN_CONTENT, note.getContent());

        // 强制使用导入的时间戳（需调用方确保非空）
        if (note.getCreateTime() != null) values.put(COLUMN_CREATE_TIME, note.getCreateTime());
        if (note.getUpdateTime() != null) values.put(COLUMN_UPDATE_TIME, note.getUpdateTime());

        long id = db.insert(TABLE_NOTES, null, values);
        db.close();
        return id;
    }

    // 获取所有笔记
    public List<Note> getAllNotes() {
        List<Note> notes = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NOTES,
                new String[]{COLUMN_ID, COLUMN_TITLE, COLUMN_CONTENT, COLUMN_CREATE_TIME, COLUMN_UPDATE_TIME},
                null, null, null, null, COLUMN_CREATE_TIME + " DESC");

        if (cursor.moveToFirst()) {
            do {
                Note note = new Note(
                        cursor.getLong(0),
                        cursor.getString(1),
                        cursor.getString(2));
                long aLong = cursor.getLong(3);// 获取创建时间
                long aLong1 = cursor.getLong(4);// 获取更新时间
                note.setCreateTime(aLong);
                note.setUpdateTime(aLong1);
                notes.add(note);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return notes;
    }

    // 更新笔记
    public int updateNote(Note note) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_TITLE, note.getTitle());
        values.put(COLUMN_CONTENT, note.getContent());
        values.put(COLUMN_UPDATE_TIME, System.currentTimeMillis() / 1000);
//        return db.update(TABLE_NOTES, values,
//                COLUMN_ID + " = ?",
//                new String[]{String.valueOf(note.getId())});

        // 仅在标题或内容不同时才更新
        String whereClause = COLUMN_ID + " = ? AND (" +
                COLUMN_TITLE + " != ? OR " +
                COLUMN_CONTENT + " != ?)";

        String[] whereArgs = new String[]{
                String.valueOf(note.getId()),
                note.getTitle(),
                note.getContent()
        };
        return db.update(TABLE_NOTES, values, whereClause, whereArgs);
    }

    // 删除笔记
    public void deleteNote(long id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_NOTES, COLUMN_ID + " = ?",
                new String[]{String.valueOf(id)});
        db.close();
    }

    // 获取单个笔记
    public Note getNote(long id) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_NOTES,
                new String[]{COLUMN_ID, COLUMN_TITLE, COLUMN_CONTENT},
                COLUMN_ID + " = ?",
                new String[]{String.valueOf(id)}, null, null, null);

        if (cursor != null && cursor.moveToFirst()) {
            Note note = new Note(
                    cursor.getLong(0),
                    cursor.getString(1),
                    cursor.getString(2));
            cursor.close();
            return note;
        }
        return null;
    }

    public void archiveNote(long id) {
//        SQLiteDatabase db = this.getWritableDatabase();
//        ContentValues values = new ContentValues();
//        values.put("is_archived", 1); // 假设有一个归档字段
//        db.update(TABLE_NOTES, values, COLUMN_ID + " = ?", new String[]{String.valueOf(id)});
//        db.close();
    }
}